package com.qingluan.Database;

import java.util.ArrayList;
import java.util.LinkedList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ReceivedMissionDB extends SQLiteOpenHelper{
	
	private static final String DB_NAME = "mission.db";
	public static final String TBL_NAME_MISSION = "missionReceive";
	private static final String creatDBmis = "create table " + TBL_NAME_MISSION + 
			" ( id integer primary key autoincrement ,  " +
			"mission_object_id  text unique not null ,  "+
			"mission_name  text not null, " +
			"picture_url text unique , "+
			"begin_time text , "+
			"mission_continue text not null, "+
			"mission_up_email text not null , "+
			"mission_accept_num text , "+
			"mission_latitude text not null, "+
			"mission_longitude text not null, "+
			"mission_tag text, "+
			"mission_place_name text not null, "+
			"attendee text, "+
			"num text ,"+
			"up_name text not null," +
			"mission_describe text )";
	private SQLiteDatabase db;

	public static  	String PICTURE = "picture_url";
	public static 	String MISSION_NAME = "mission_name";
	public static 	String MISSION_UP_EMAIL = "mission_up_email";
	public static 	String LATITUDE = "mission_latitude";
	public static 	String LONGITUDE = "mission_longitude";
	public static 	String MISSION_TAG = "mission_tag";
	public static 	String ATTENDEE = "attendee";
	public static 	String UP_NAME = "up_name";
	public static 	String DESCRIBE = "mission_describe";
	public static 	String ACCESS_NUM = "mission_accept_num";
	public static 	String TIME_S = "begin_time";
	public static 	String TIME_C = "mission_continue";
	public static 	String MISS_OBJ_ID = "mission_object_id";
	public static 	String PLACE_NAME = "mission_place_name";
	public static 	String NUM_PAR = "num";

	

	public ReceivedMissionDB(Context context) {
		super(context, ReceivedMissionDB.DB_NAME,null,3);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d("CACHE",creatDBmis);
		this.db = db;
		this.db.execSQL(creatDBmis);
	}

	
	/** 
	 * add some info to our DB
	 */
	
	public void insertMission (ContentValues values){
		SQLiteDatabase db = getWritableDatabase();
		
		db.insert(MissionDataBase.TBL_NAME_MISSION, null, values);
		Log.d("CACHE",values.toString()+"/n"+db.toString());
		if (db != null){
			db.close();
		}
	}
	
	
	
	/*
	 * Base method supported for above methods.
	 */
	private Cursor query(String tableName){
		SQLiteDatabase db = getWritableDatabase();
		Cursor c  = db.query(tableName, null, null, null, null, null, null, null);
		
		
		return c;
	}
	
	
	
	
	
	
	
	
	/*
	 * this is base method for more detail searcher;
	 */
	public String search(String someString,String type,String resultType){
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		if(c.getCount() == 0){
			SQLiteDatabase db = getWritableDatabase();
			c.close();
			db.close();
			return null;
		}
		if (c.getString(c.getColumnIndex(type)).equals(someString)){
			String value = c.getString(c.getColumnIndex(resultType));
			c.close();
			SQLiteDatabase db = getWritableDatabase();
			db.close();
			
			return value;
			
		}
		
		while(c.moveToNext()){
			String value = null;
			if (c.getString(c.getColumnIndex(type)).equals(someString)){
				value = c.getString(c.getColumnIndex(resultType));
				Log.d("database_work_mission","found in Miss_db :"+value);
				c.close();
				SQLiteDatabase db = getWritableDatabase();
				db.close();
				
				return value;
				
			}
		}
		
		
		return null;
	}
	
	/*
	 * other important method to get Searcher key --- uid.
	 */
	public String getEmailByType(String type,String type_info){
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		if(c.isLast()){
			c.close();
			SQLiteDatabase db = getWritableDatabase();
			db.close();
			return null;
		}
		if (c.getString(c.getColumnIndex(type)).equals(type_info)){
			String value = c.getString(c.getColumnIndex(MISSION_UP_EMAIL));
			c.close();
			SQLiteDatabase db = getWritableDatabase();
			db.close();
			if (value != null){
				return value;
			}
		}
		
		while(c.moveToNext()){
			String value = null;
			if (c.getString(c.getColumnIndex(type)).equals(type_info)){
				value = c.getString(c.getColumnIndex(MISSION_UP_EMAIL));
				Log.d("database_work_mission","found in Miss_db :"+value);
				c.close();
				SQLiteDatabase db = getWritableDatabase();
				db.close();
				if (value != null){
					return value;
				}
			}
		}
		
		
		return null;
	}
	
	
	
	
	
	
	
	
	/**
	 * 
	 * 
	 * 
	 * 
	 * 
	 * *
	 *  get count of db
	 */
	public int count(){
		SQLiteDatabase db = getWritableDatabase();
		Cursor c = this.query(TBL_NAME_MISSION);
		int size = c.getCount();
		c.close();
		db.close();
		return size;
	}
	
	
	
	
	
	
	
	
	
	
	/**
	 * 
	 * 
	 * 
	 *  main method to search something ,base on below method
	 * 
	 * 
	 * 
	 */
	public String SearcherByEmailAndTime(String email,String time,String resultType){
		LinkedList<String> allMissionInEmail = new LinkedList<String>();

		SQLiteDatabase db = getWritableDatabase();
		
		
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		if(c.isLast()){
			c.close();
			db.close();
			return null;
		}
		if (c.getString(c.getColumnIndex(MISSION_UP_EMAIL)).equals(email)){
			String value = c.getString(c.getColumnIndex(resultType));
			allMissionInEmail.add(value);
		}
		
		while(c.moveToNext()){
			String value = null;
			if (c.getString(c.getColumnIndex(MISSION_UP_EMAIL)).equals(email)){
				value = c.getString(c.getColumnIndex(resultType));
				allMissionInEmail.add(value);
			}
		}
		String targetString  = null;
		if(allMissionInEmail.size() == 0){
			c.close();
			db.close();
			return null;
		}
		
		ArrayList<String> tempString = SearcherByTime(time, resultType);
		if(tempString.isEmpty()){
			return null;
		}
		for (String i : tempString){
			for (String i2 :allMissionInEmail)
			if (i.equals(i2)  ){
				targetString = i;
			}
		}
		c.close();
		db.close();
		
		return targetString;
	}
	
	public ArrayList<String> SearcherByTime(String time,String type){
		SQLiteDatabase db = getWritableDatabase();
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		ArrayList<String> itemsArrayList = new ArrayList<String>();
		if(c.isLast()){
			c.close();
			db.close();
			return null;
		}
		if (c.getString(c.getColumnIndex(TIME_S)).equals(time)){
			String value = c.getString(c.getColumnIndex(type));
			itemsArrayList.add(value);
		}
		
		while(c.moveToNext()){
			String value = null;
			if (c.getString(c.getColumnIndex(TIME_S)).equals(time)){
				value = c.getString(c.getColumnIndex(type));
				Log.d("database_work_mission","found in Miss_db :"+value);
				
				itemsArrayList.add(value);
			}
		}
		return itemsArrayList;
	}

	
	
	
	
	
	
	/*
	 * some update data method
	 */
	public void UpdateByOid (String obj_id ,String typeString  ,String value){
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("update "+TBL_NAME_MISSION+" set "+typeString+" = '"+value+"' where "+MISS_OBJ_ID+"= '"+obj_id+"' ");
		//int result = db.execSQL(TBL_NAME_MISSION, values, "mission_object_id='"+obj_id+"'", null);
		db.close();
		//return result;
	}
	
	
	
	
	
	
	
	/*
	 * special foundation methods area
	 * 
	 * 
	 * include :
	 * public String[]  getObjectIds()   #get all Object's id
	 * public String[] 	getObjectIdsByEmail() #get all mission by one email.
	 */
	public ArrayList<String> getObjectIds(){
		SQLiteDatabase db = getWritableDatabase();
		ArrayList<String> IDS = new ArrayList<String>();
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		if (c.getString(c.getColumnIndex(MISS_OBJ_ID)).isEmpty()){
			c.close();
			db.close();
			return null;
		}else {
			IDS.add(c.getString(c.getColumnIndex(MISS_OBJ_ID)));
		}
		while (c.moveToNext()){
			IDS.add(c.getString(c.getColumnIndex(MISS_OBJ_ID)));
		}
		db.close();
		return IDS;
	}
	
	public ArrayList<String> getObjectIdsByEmail(String email){
		ArrayList<String> IDS = new ArrayList<String>();
		
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		if (c.isLast()){
			return null;
		}
		
		if (c.getString(c.getColumnIndex(MISSION_UP_EMAIL)).equals(email)){
			IDS.add(c.getString(c.getColumnIndex(MISS_OBJ_ID)));
		}
		while(c.moveToNext()){
			if (c.getString(c.getColumnIndex(MISSION_UP_EMAIL)).equals(email)){
				IDS.add(c.getString(c.getColumnIndex(MISS_OBJ_ID)));
			}
		}
		return IDS;
	}
	
	public String searchById(String obj_id , String type){
		SQLiteDatabase db = getWritableDatabase();
		Cursor c = this.query(TBL_NAME_MISSION);
		c.moveToFirst();
		if (c.getCount() == 0){
			c.close();
			db.close();
			return null;
		}
		if (c.getString(c.getColumnIndex(MISS_OBJ_ID)).equals(obj_id)){

			String resString = c.getString(c.getColumnIndex(type));
			
			c.close();
			db.close();
			return resString;
		}
		
		while(c.moveToNext()){
			if (c.getString(c.getColumnIndex(MISS_OBJ_ID)).equals(obj_id)){
				String resString = c.getString(c.getColumnIndex(type));

				c.close();
				db.close();
				return 	resString;
			}
						
		}
		return null;
	}
	
	
	
	
	
	
	
	
	
	/*
	 * base foundation delete item  
	 */
	public void del(String tableName,String obj_id ){
		if (db == null){
			db = getWritableDatabase();
			
		}
		db.delete(tableName, MISS_OBJ_ID+"=?", new String[] {obj_id});
		if (db != null){
			db.close();
		}
	}
	
	public void close(){
		if (db != null){
			db.close();
		}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	

}
